package com.application.util;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteConstraintException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import android.widget.Toast;
import com.application.bean.Student;

import java.util.ArrayList;
import java.util.List;

public class MySQLiteOpenHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = "school.db";
    private static final int DATABASE_VERSION = 2;
    private static final String TABLE_NAME_STUDENT = "student";
    private static final String CREATE_TABLE_SQL = "create table " + TABLE_NAME_STUDENT + " (id integer primary key autoincrement,name text,number text UNIQUE,gender text,score text);";

    private Context toastText;
    public MySQLiteOpenHelper(Context context) {
        super(context, DB_NAME, null, DATABASE_VERSION);
        this.toastText=context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_SQL);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public long insertData(Student student) {
        long rowId = -1;
        SQLiteDatabase db = getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put("name", student.getName());
        values.put("number", student.getNumber());
        values.put("gender", student.getGender());
        values.put("score", student.getScore());
        try {
            rowId = db.insertOrThrow(TABLE_NAME_STUDENT, null, values);
        } catch (SQLiteConstraintException e) {
            rowId=-2;
            e.printStackTrace();
        } finally {
            db.close();
        }
        return rowId;
    }

    public int deleteFromDbByNameAndNumber(String name, String number) {
        SQLiteDatabase db = getWritableDatabase();
        return db.delete(TABLE_NAME_STUDENT, "name like ? and number = ?", new String[]{name, number});
    }

    public int updateData(Student student) {

        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", student.getName());
        values.put("number", student.getNumber());
        values.put("score", student.getScore());

        return db.update(TABLE_NAME_STUDENT, values, "name = ? and number = ?", new String[]{student.getName(), student.getNumber()});
    }

    public Student queryFromDbByNumber(String number) {
        SQLiteDatabase db = getWritableDatabase();
        Cursor cursor = db.query(TABLE_NAME_STUDENT, null, "number = ?", new String[]{number}, null, null, null);
        Student student = null;

        if (cursor.moveToFirst()) {
            student = new Student();
            student.setNumber(cursor.getString(cursor.getColumnIndexOrThrow("number")));
            student.setName(cursor.getString(cursor.getColumnIndexOrThrow("name")));
            student.setGender(cursor.getString(cursor.getColumnIndexOrThrow("gender")));
            student.setScore(cursor.getString(cursor.getColumnIndexOrThrow("score")));
        }
        cursor.close();
        return student;
    }

    public List<Student> queryAllData() {
        SQLiteDatabase db = getWritableDatabase();
        Cursor cursor = db.query(TABLE_NAME_STUDENT, null, null, null, null, null, "number ASC");
        List<Student> studentList = new ArrayList<>();

        if (cursor.moveToFirst()) {
            do {
                Student student = new Student();
                student.setNumber(cursor.getString(cursor.getColumnIndexOrThrow("number")));
                student.setName(cursor.getString(cursor.getColumnIndexOrThrow("name")));
                student.setGender(cursor.getString(cursor.getColumnIndexOrThrow("gender")));
                student.setScore(cursor.getString(cursor.getColumnIndexOrThrow("score")));
                studentList.add(student);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return studentList;
    }

}
